//
// Created by henry on 2025/11/7.
//

#include "database.h"

//定义数据库句柄
sqlite3 * db = NULL;

// 打开数据库
int open_db(){
    int rc = sqlite3_open("supermarket.db",&db);
    if(rc != SQLITE_OK){
        fprintf(stderr,"open_db:打开数据库失败:%s\n", sqlite3_errmsg(db));
        return -1;
    }
    printf("open_db:连接数据库成功!\n");
    return 0;
}

// 初始数据库，建表
int init_db(){
    open_db(); // 打开数据库连接
    if (create_card() != SQLITE_OK){ // 会员卡表
        return -1;
    }
    if (create_orders() != SQLITE_OK){ // 商品购买订单表
        return -1;
    }
    if (create_user() != SQLITE_OK){ // 用户、员工、管理员表
        return -1;
    }
    if (create_user_info() != SQLITE_OK){ // 用户、员工、管理员信息表
        return -1;
    }
    if (create_goods() != SQLITE_OK){ // 商品表
        return -1;
    }
    if (create_purchase() != SQLITE_OK){ // 商品进货表
        return -1;
    }
    if (create_category() != SQLITE_OK){ // 商品分类表
        return -1;
    }
    close_db(); // 关闭数据库
    printf("init_db:数据库初始化完成!\n");
    return 0;
}

// 会员卡表
int create_card(){
    const char *sql = "drop table if exists card;"
            "create table if not exists card("
            " id integer primary key,"
            " user_id integer,"
            " balance real default 0,"
            " level integer default 0,"
            " total_spent real default 0,"
            " create_time text"
            ");"
            "insert into card(user_id,balance,level,total_spent,create_time) "
            "values(1,500000,3,10000,date()),"
            "      (2,20000,1,5000,date()),"
            "      (3,5000,0,0,date());";
    char *errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc != SQLITE_OK){
        fprintf(stderr,"create_card:创建表失败:%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("create_card:创建成功!\n");
    return 0;
}

// 订单表
int create_orders(){
    const char *sql = "drop table if exists orders;"
            "create table if not exists orders("
            " id integer primary key,"
            " user_id integer not null,"
            " goods_id integer not null,"
            " number integer not null,"
            " status integer default 0,"
            " pay_time text not null,"
            " emp_id integer not null,"
            " create_time text"
            ");";
    char *errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc != SQLITE_OK){
        fprintf(stderr,"create_orders:创建表失败:%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("create_orders:创建成功!\n");
    return 0;

}

// 商品表
int create_goods(){
    const char * sql = "drop table if exists goods;"
            " create table if not exists goods("
            " id integer primary key,"
            " goods_name text not null,"
            " price real not null,"
            " number integer not null,"
            " category_id integer,"
            " expiration_time text,"
            " status integer default 0,"
            " purchase_id integer"
            ");";
    int rc = sqlite3_exec(db ,sql,NULL,NULL,NULL);
    if (rc != SQLITE_OK){
        fprintf(stderr,"create_goods:创建表失败:%s\n",sqlite3_errmsg(db));
        return -1;
    }
    printf("create_goods:创建成功!\n");
    return 0;
}

// 商品进货表
int create_purchase(){
    const char * sql = "drop table if exists purchase;"
            "create table if not exists purchase("
            " id integer primary key,"
            " goods_name text not null,"
            " cost real not null,"
            " number integer not null,"
            " category_id integer,"
            " address text,"
            " purchase_time text"
            ");";
    int rc = sqlite3_exec(db ,sql,NULL,NULL,NULL);
    if (rc != SQLITE_OK){
        fprintf(stderr,"create_purchase:创建表失败:%s\n",sqlite3_errmsg(db));
        return -1;
    }
    printf("create_purchase:创建成功!\n");
    return 0;
}

// 商品分类
int create_category(){
    const char * sql = "drop table if exists category;"
            " create table if not exists category( "
            " id integer primary key,"
            " category_name text not null"
            ");"
            "insert into category(category_name) "
            "values('生鲜食品'),('乳制品'),('烘焙食品'),"
            "('饮料'),('调味品'),('零食'),('文具用品'),"
            "('电子产品'),('清洁用品'),('冷冻食品');";
    int rc = sqlite3_exec(db ,sql,NULL,NULL,NULL);
    if (rc != SQLITE_OK){
        fprintf(stderr,"create_category:创建表失败:%s\n",sqlite3_errmsg(db));
        return -1;
    }
    printf("create_category:创建成功!\n");
    return 0;
}

// 创建用户表
int create_user(){
    const char * sql = "drop table if exists user;"
            "create table if not exists user("
            " id integer primary key,"
            " username text not null,"
            " password text not null,"
            " role integer default 2,"
            " status integer default 0"
            ");"
            "insert into user(username,password,role)"
            "values('admin','admin',0),"
            "      ('zhangsan','123',1),"
            "      ('lisi','123',2);";
    int rc = sqlite3_exec(db,sql,NULL,NULL,NULL);
    if(rc != SQLITE_OK){
        fprintf(stderr, "create_user:创建表失败:%s\n", sqlite3_errmsg(db));
        return -1;
    }
    printf("create_user:创建成功!\n");
    return 0;
}

// 创建用户信息表
int create_user_info(){
    const char * sql = "drop table if exists user_info;"
                       "create table if not exists user_info("
                       " user_id integer not null,"
                       " name text not null,"
                       " sex text not null,"
                       " age integer not null,"
                       " phone text not null unique,"
                       " create_time text not null"
                       ");"
                       "insert into user_info(user_id,name,sex,age,phone,create_time) "
                       "values(1,'管理员','男',18,000001,date()),"
                       "      (2,'员工张三','男',20,000002,date()),"
                       "      (3,'用户李四','女',22,000003,date());";
    char * err_msg = NULL;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&err_msg);
    if(rc != SQLITE_OK){
        fprintf(stderr, "create_user_info:创建表失败:%s\n", err_msg);
        sqlite3_free(err_msg);
        return -1;
    }
    printf("create_user_info:创建成功!\n");
    return 0;
}

//关闭数据库
int close_db(){
    if(db != NULL){
        int rc = sqlite3_close(db);
        if (rc != SQLITE_OK){
            fprintf(stderr, "close_db:关闭数据库失败:%s\n", sqlite3_errmsg(db));
            db = NULL;
            return -1;
        }
        printf("close_db:关闭数据库成功!\n");
    }
    return 0;
}
